CREATE VIEW [dbo].[vSoaInvoiceLineCashBase] WITH SCHEMABINDING
AS
SELECT CAST(s.[ID] + ':' + s.[PRODUCT_CODE] AS VARCHAR(31)) AS InvoiceLineId,
       CAST('CASH:' + s.[ID] AS VARCHAR(20)) AS InvoiceId,
       s.[INVOICE_LINE_NUM] AS LineNumber,
       s.[PRODUCT_CODE] AS ItemId,
       s.[ID] AS ShipToPartyId,
       s.[COPIES] AS Quantity,
       (s.[PAYMENT_AMOUNT] + s.[ADJUSTMENT_AMOUNT]) AS PaymentAdjustmentAmount,
       s.[BILL_COPIES],
       s.[BILL_AMOUNT],
       s.[BALANCE],
       s.[PROD_TYPE],
       s.[BILL_DATE]
FROM  [dbo].[Subscriptions] s
WHERE s.[INVOICE_REFERENCE_NUM] = 0 AND s.[STATUS] = 'A' AND (s.[BILL_DATE] IS NOT NULL OR s.[BILL_AMOUNT] != 0)


GO
CREATE UNIQUE CLUSTERED INDEX [PK_vSoaInvoiceLineCashBase] ON [dbo].[vSoaInvoiceLineCashBase] ([InvoiceLineId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaInvoiceLineCashBase_InvoiceId] ON [dbo].[vSoaInvoiceLineCashBase] ([InvoiceId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaInvoiceLineCashBase_ShipToPartyId] ON [dbo].[vSoaInvoiceLineCashBase] ([ShipToPartyId]) ON [PRIMARY]
GO